unit Class_Course;

interface
uses
  Classes,SysUtils,ADODB;
type Tcourse=class
  private
    Fcourse_id : Integer;
    Fcourse_code : string;
    Fcourse_name : string;
    Fstud_id :Integer;

  public
    procedure Insert(aADOConnetion:TADOConnection);
    procedure update(aADOConnetion:TADOConnection);
    procedure delete(aADOConnetion:TADOConnection);
    class procedure select(aADOConnetion:TADOConnection;var aList:TStringList;aSQL:string='');
  public

    function  GetNextCourseID(aADOConnetion:TADOConnection):Integer;
    function  SelectCourseIDByCourseCode(aADOConnetion:TADOConnection):Integer;
  published
    property course_id : Integer read Fcourse_id write Fcourse_id;
    property course_code : string read Fcourse_code write Fcourse_code;
    property course_name : string read Fcourse_name write Fcourse_name;
    property stud_id : Integer read Fstud_id write Fstud_id;
  public
    destructor Destroy; override;
    constructor Create;
  end;
implementation

{ Tcourse }

constructor Tcourse.Create;
begin

end;

procedure Tcourse.delete(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
   try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;
  with cADOC do
  begin
    cSQL := 'DELETE tbl_course where course_code=:course_code ';
    CommandText := cSQL;

    Parameters.ParamByName('course_code').Value := self.course_code;
  end;

  cADOC.Execute;
  finally
    FreeAndNil(cADOC);
end;
end;

destructor Tcourse.Destroy;
begin

  inherited;
end;

function Tcourse.GetNextCourseID(aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select max(course_id)+1 as max_id from tbl_course';
    cADOD.Open;

    Result := cADOD.FieldByName('max_id').AsInteger;

    if Result = 0 then Result := 1;
  finally
    FreeAndNil(cADOD);
  end;
end;

procedure Tcourse.Insert(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

    with cADOC do
    begin
      cSQL := 'insert into tbl_course (course_id,course_code,course_name) values (:course_id,:course_code,:course_name)';
      CommandText := cSQL;

      Parameters.ParamByName('course_id').Value := self.course_id;
      Parameters.ParamByName('course_code').Value := self.course_code;
      Parameters.ParamByName('course_name').Value := self.course_name;
    end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

class procedure Tcourse.select(aADOConnetion: TADOConnection;
  var aList: TStringList; aSQL: string);
var
  ccourse:Tcourse;
  ADOD:TADODataSet;
begin
  if aList = nil then Exit;

  try
    ADOD := TADODataSet.Create(nil);
    ADOD.Connection := aADOConnetion;
    if aSQL = '' then
    begin
      aSQL := 'select * from tbl_course ';
    end;

    ADOD.CommandText := aSQL;
    ADOD.Open;

    ADOD.First;
    while not ADOD.Eof do
    begin
      ccourse := Tcourse.Create;
      aList.AddObject('',ccourse);

      ccourse.course_ID := ADOD.FieldByName('course_ID').AsInteger;
      ccourse.course_Code := ADOD.FieldByName('course_Code').AsString;
      ccourse.course_Name := ADOD.FieldByName('course_Name').AsString;
      ADOD.Next;
    end;

  finally
    FreeAndNil(ADOD);
  end;
  
end;

function Tcourse.SelectCourseIDByCourseCode(aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  Result := -1;
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select course_id from tbl_course where course_code=:course_code';
    with cADOD.Parameters do
    begin
      ParamByName('course_code').Value := self.course_code;
    end;
    cADOD.Open;

    Result := cADOD.FieldByName('course_id').AsInteger;

    if Result = 0 then Result := -1;
  finally
    FreeAndNil(cADOD);
  end;
end;

procedure Tcourse.update(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

    with cADOC do
    begin
      {cSQL := 'UPDATE tbl_course set course_name=:course_name from tbl_grade where tbl_course.course_id=tbl_grade.course_id and tbl_grade.stud_id=:stud_id';
      CommandText := cSQL;
      Parameters.ParamByName('stud_id').Value := self.stud_id;
      Parameters.ParamByName('course_name').Value := Self.course_name;}
      cSQL := 'UPDATE tbl_course set course_name=:course_name where course_code=:course_code';
      CommandText := cSQL;
      Parameters.ParamByName('course_code').Value := self.course_code;
      Parameters.ParamByName('course_name').Value := Self.course_name;
    end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

end.
